In LISTSERV Maestro, calculation formulas are available in the context of the special “
*Calc” system drop-in (see Section 15.2.1
System Drop-Ins). This section describes the syntax and semantics of these calculation formulas.
A formula is a sequence of expressions that are combined with operators into more complex expressions. Expressions can optionally be nested with parenthesis and make use of a few pre-defined functions.
Restriction: None. Constant number expressions can be used in any formula.
Type: Constant number expressions are, as the name implies, always of the type “number”.
Usage: To include a constant number in a formula, simply type the number using only the digits “
0“-“
9” (do not use “
,” as a thousands-separator or “
.” as a decimal point). Negative numbers must have a leading minus sign “
-“.
Restriction: None. Constant text literal expressions can be used in any formula.
Type: Constant text literal expressions are, as the name implies, always of the type “text”.
Usage: To include a text literal in a formula, type the characters desired, and then denote the beginning and the end of the text literal by enclosing the whole string in quotation marks <
">. There must not be any linebreaks in the text string.
"example"
"this is a text string"
"This string contains ""quotes"" which are therefore escaped"
" this string has three spaces at the beginning and end "
Restriction: Standard merge field expressions are only allowed if the formula is used in a “
*Calc” system drop-in of a job with the recipients type
|
•
|
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
|
If the system drop-in is used in a job with a different recipient type, then the formula must not contain any merge field expressions (if it does, an error message will appear during test delivery).
Type: Standard merge field expressions can either be the
number or
text type, depending on their context or content (see the explanation for
Type Determination for details).
Usage: To include a standard merge field in a formula, type the merge field name enclosed in “
&” and “
;”. Only merge field names that are actually defined in the recipient list can be used.
&NAME;
&STATE;
&ACCOUNT_BALANCE;
Type Determination: Whenever a merge field appears in a formula, it will first be replaced with the field value for the current recipient before the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined as follows:
|
•
|
If the formula is used in a “*Calc” system drop-in together with recipients based on a “Hosted Recipient List” target group, then the type of each merge field is already defined by the profile field’s type, which was defined during creation of the associated hosted recipient list:
|
|
•
|
Profile fields of the Boolean and Multiple Selection types are not allowed to be used in formulas at all.
|
|
•
|
If the formula is used in a “*Calc” system drop-in with the recipients types as uploaded CSV text file or LISTSERV Maestro selects from a database, then the type of the field is determined by the field’s content as follows:
|
Special Note: If the formula is used in a “*Calc” system drop-in together with recipients based on a “Hosted Recipient List” target group, then only profile fields which are defined in the hosted recipient list as mandatory are allowed as “standard merge field” expressions. Fields that are defined as optional must use the “optional merge field” expression instead. (See next sub-section.)
Restriction: Optional merge field expressions are only allowed if the formula is used in a “
*Calc” system drop-in of a job with the recipients type
|
•
|
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
|
If the system drop-in is used in a job with a different recipient type, the formula must not contain any merge field expressions (if it does, an error message will display during test delivery).
Type: Optional merge field expressions can be of the
number or
text type, depending on their context and content (see the explanation of Type Determination for details).
Usage: Optional merge fields are fields that may be undefined (empty) for at least some recipients in the recipient list. Recipients with a blank field would not have the result of the calculation formula displayed in their content.
The whole expression is enclosed in brackets “[“ and “
]”. Between the brackets first type the name of the merge field being addressed, enclosed with “
&” and “
;”. Next, type the default that will be used for all recipients where the merge field itself is undefined.
The default that is replaced by “DEFAULT” as shown above must be a
constant number or a
constant text literal.
Type Determination: Whenever any such merge field appears in a formula, it will first be replaced with the field value for the current recipient, or if that value is undefined or empty, it will be replaced with the given default. Then the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined just like for “standard merge field” expressions.
In addition, if the field’s value is empty or undefined, the type will be determined by the type of the given default. Make sure that the type of the default matches the field’s type or is at least convertible into that type (see
Automatic Type-Conversion).
Restriction: Functions can usually be used in any formula. However, there may be individual restrictions that apply only to certain functions. See the function descriptions for details.
Type: The type depends on each function. See the function descriptions for details.
Usage: To include a function in a formula, simply type the function name possibly followed by an argument list in parenthesis. See the function description for details concerning whether arguments are required by a certain function and what they mean.
Note: Function names are case sensitive and any parenthesis enclosing the argument list must immediately follow the function name, without any space in between.
Abs(-20)
Max(15, &AGE;)
ToLower("Convert this string to ALL Lowercase")
Min(-10, -11, &SIZE_A;, &SIZE_B;)
Available Functions: The currently available functions are described in
Formula Functions.
Operators always work on two operands, which may be any valid expressions. Some operators require their operand expressions to be either the
number or
text type.
+ Addition operator (sum of both operands)
- Subtraction operator (difference of both operands)
* Multiplication operator (product of both operands)
/ Integer-Division operator (integer-quotient of both operands)
% Modulo operator (remainder of integer-division of both operands)
Note: In LISTSERV Maestro, the division is strictly an integer division. Any decimal places in the result are discarded; they are
not rounded off, but simply ignored.
+ Concatenation operator (appends both operands)
The concatenation operator takes the two text operands and simply appends the text string on the right-hand to the end of the text string on the left-hand. The result is one string that contains the two strings concatenated together.
The character “+” is used both to denote the addition operator for
number operands as well as the concatenation operator for
text operands. So if a “
+” appears anywhere in the formula, LISTSERV Maestro must first determine if in this context the addition or the concatenation operator is meant. It simply does so by looking at the operands. If both operands are the
number type, then the “
+” is interpreted as the addition operator and the result will also be the
number type.
If at least one of the operands is of the text type, then the “
+” is interpreted as the concatenation operator and the result will be of the
text type. If the other operand is the
number type, it is automatically converted to
text first, see
Automatic Type-Conversion.
The multiplication/division-type operators (“
*”, “
/” and “
%”) have higher precedence than the
addition/subtraction-type operators (“
+” and “
–”).
Operators with higher precedence are processed first, if several operators with the same precedence level are encountered. They are processed from left to right. Parenthesis can be set freely to influence precedence: Inner parenthesis will be processed before outer parenthesis.
Operators and functions usually require their operands and arguments to be of a certain type. However, under one circumstance it is possible to supply an expression of a different type as an operand or argument, which will then automatically be converted to the required type:
Whenever an operand or argument of the text type is required, an expression of the
text or
number type can be used. In the latter case, the
number will first be converted into a
text before the operator or function is applied. For this conversion, the number-value is simply converted into the corresponding string representation, for example the number 157 becomes the text string
"157".
The format of the date and time patterns must be specified for the functions “ToDate” and “
ToMillis” to convert a numerical date/time value (represented in milliseconds since “Jan. 1
st 1970, 00:00:00 GMT”) into a formatted output string or vice versa.
The pattern format described here applies to the formatting process, when a numerical date/time value is converted into a formatted text string, and to the parsing process, when a text string is parsed to convert it back into the numerical date/time value.
Important: For date/time formatting and parsing, by default the U.S. locale and the current time zone of the server where the Maestro User Interface (LUI) component is running is used. This means that if locale specific values (names of months, weekdays, era-designators, and the like) are required, they will be given as the U.S. locale values by default. Similarly, if a time is given, it will be formatted (or interpreted) as relative to the time zone of the server (although for parsing a specific time zone can be supplied). If the default U.S. locale is not desired, specify a locale in the “
ToDate” or “
ToMillis” function.
Date and time formats are specified by pattern strings. Within pattern strings, unquoted letters from ‘
A’ to ‘
Z’ and from ‘
a’ to ‘
z’ are interpreted as pattern letters representing the components of a date or time string (see below for details). Text can be quoted using single quotes <
'> to avoid interpretation. In a quoted text, the double single quote <
''> represents a single quote. All other characters are not interpreted; they are simply copied into the output string during formatting or matched against the input string during parsing.
The following pattern letters are defined (all other characters from ‘A’ to ‘
Z’ and from ‘
a’ to ‘
z’ are reserved). The letters are case-sensitive. The same letter has different meanings in its uppercase or lowercase version. Each pattern letter has a specific “presentation” in the created final string (during formatting) or in the parsed string (during parsing). For example, presentation types may be
Text,
Number,
Year or similar. More details about the presentations and their meanings follow below.
Note: The
calendar week in year and
calendar week in month value depends on the locale that is used. The locale determines the conventions about which weekday is interpreted as the first day of the week (usually “Monday” or “Sunday”) and under which circumstances a week that falls partially into one year (or month) and partially into the next, is interpreted as belonging to the one year (or month) or the other.
Note: The
weekday ordinal in month value indicates the ordinal number of the weekday of the given date/time in the given month. For the first Monday in a month, the ordinal is “1”, as it is for the first Tuesday, Wednesday and so on. For the second Monday in a month, the ordinal is “2”, and so on.
|
•
|
Text: For formatting, if the number of pattern letters is four or more, the full form is used; otherwise, a short or abbreviated form is used if available.
|
|
•
|
Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.
|
|
•
|
Year: For formatting, if the number of pattern letters is two, the year is truncated to 2 digits; otherwise it is interpreted as a Number (see above).
|
For parsing with the abbreviated year pattern (“y” or “
yy”), LISTSERV Maestro must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the current time. For example, using the pattern “
MM/dd/yy” on Jan. 1, 1997, the text “
01/11/12” would be interpreted as Jan. 11, 2012, while the text “
05/04/64” would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that is not all digits (for example “
-1”), is interpreted literally. Therefore, “
01/02/3” or “
01/02/003” are parsed, using the same pattern, as Jan. 2, 3 AD. Likewise, “
01/02/-3” is parsed as Jan. 2, 4 BC.
|
•
|
Month: If the number of pattern letters is one or two, the month is interpreted as Number, if it is 3 or more, it is interpreted as Text. Therefore, if the month is interpreted as Number or Text, the applicable Number/ Text interpretation rules apply (see above). For example: 1 letter will be a Number that is not padded, 2 letters will be a Number that is padded, 3 letters will be a Text using the abbreviated form and 4 or more letters will be a Text using the long form.
|
|
•
|
General time zone: For formatting, the time zone is handled as Text if it has a name. If not, it is given as a GMT offset value in the format “ GMT[+|-]HH:MM”, where “ HH” is the hours between 0 and 23 (one or two digits, may be zero-padded to the left) and “ MM” is the minutes between 00 and 59 (always two digits, zero-padded to the left if necessary). For example, “ GMT+8:00”, “ GMT+08:00”, “ GMT-12:45”.
|
[+|-]HHMM”, where “
HH” is the hours as two digits, between 00 and 23 (zero-padded to the left if necessary) and “
MM” is the minutes as two digits, between 00 and 59 (zero-padded to the left if necessary).
|
•
|
Time zone parsing: For parsing of a time zone, it does not matter if the format pattern specifies a “General time zone” or a “RFC 822 time zone”: In both cases, all three types of time zone specifications are accepted:
|
The following examples show how date and time patterns are interpreted in the default U.S. locale with the “U.S. Pacific Time” time zone. The given date and time are “
2001-07-04 12:08:56” local time in that time zone.
When specifying a locale to be used for date/time formatting in “ToDate” or for parsing in “
ToMillis”, you can either specify the specific ISO codes of the desired country and language (see the description of “
ToDate” and “
ToMillis), or you can simply specify one of the following predefined locale names, which stands as a shortcut for the given language and country combination:
